create or replace view vac_application_auth_4_1 as
with au as
(
select
 workspace,
 workspace_display_name,
 application_id,
 application_name,
 is_current_authentication,
 is_subscribed,
 scheme_type,
 scheme_type_code,
 authentication_scheme_name,
 invalid_session_type,
 invalid_session_url,
--
 ac_utils.hs(plsql_code ) plsql_code_hs,
 dbms_lob.substr(plsql_code ,4000, 1)        plsql_code_sr1,
 dbms_lob.substr(plsql_code ,4000, 4000+1 )  plsql_code_sr2,
 dbms_lob.substr(plsql_code ,4000, 8000+1 )  plsql_code_sr3,
 dbms_lob.substr(plsql_code ,4000, 12000+1)  plsql_code_sr4,
--
 verification_function,
 pre_authentication_process,
 post_authentication_process,
--
 cookie_name,
 cookie_path,
 cookie_domain,
 cookie_secure,
 logout_url,
 attribute_01,
 attribute_02,
 attribute_03,
 attribute_04,
 attribute_05,
 attribute_06,
 attribute_07,
 attribute_08,
 attribute_09,
 attribute_10,
 attribute_11,
 attribute_12,
 attribute_13,
 attribute_14,
 attribute_15,
 help_text,
 comments,
 authentication_scheme_id,
 referenced_schema_id,
 last_updated_by,
 last_updated_on,
 created_by,
 created_on,
 component_signature
from
APEX_APPLICATION_AUTH t
)
select
  workspace,
  application_id,
  application_name,
  authentication_scheme_name,
--
  to_clob(plsql_code_sr1)    || to_clob(plsql_code_sr2)
  || to_clob(plsql_code_sr3) || to_clob(plsql_code_sr4) plsql_code_sr,
--
  authentication_scheme_id,
  last_updated_by,
  last_updated_on,
  'APEX_APPLICATION_AUTH' src_view_name,
  u_name,
  u_value
from
(
select
  workspace,
  workspace_display_name,
  application_id,
  application_name,
  authentication_scheme_name,
--
  plsql_code_sr1,
  plsql_code_sr2,
  plsql_code_sr3,
  plsql_code_sr4,
--
  authentication_scheme_id,
  last_updated_by,
  last_updated_on,
  created_by,
  created_on,
  'APEX_APPLICATION_AUTH' src_view_name,
  u_name,
  u_value
from au
 UNPIVOT INCLUDE NULLS ( u_value
   FOR    u_name
   IN
   ( is_current_authentication,
     is_subscribed,
     scheme_type,
     scheme_type_code,
     invalid_session_type,
     invalid_session_url,
     plsql_code_HS,
     verification_function,
     pre_authentication_process,
     post_authentication_process,
     cookie_name,
     cookie_path,
     cookie_domain,
     cookie_secure,
     logout_url,
     attribute_01,
     attribute_02,
     attribute_03,
     attribute_04,
     attribute_05,
     attribute_06,
     attribute_07,
     attribute_08,
     attribute_09,
     attribute_10,
     attribute_11,
     attribute_12,
     attribute_13,
     attribute_14,
     attribute_15,
     help_text,
     comments
   )
 )
);

